package com.crud.generator.parser;

import com.crud.generator.model.TableMetadata;
import com.crud.generator.model.ColumnMetadata;
import com.crud.generator.model.IndexMetadata;
import com.crud.generator.exception.SqlParseException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.Index;
import org.springframework.stereotype.Component;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.HashMap;
import java.util.regex.Pattern;
import java.util.regex.Matcher;

/**
 * SQL DDL 解析器
 * 
 * @author CRUD-Generator
 */
@Component
@Slf4j
public class SqlDdlParser {
    
    /** 数据库类型到Java类型的映射 */
    private static final Map<String, String> TYPE_MAPPING = new HashMap<>();
    
    static {
        // 整数类型
        TYPE_MAPPING.put("TINYINT", "Integer");
        TYPE_MAPPING.put("SMALLINT", "Integer");
        TYPE_MAPPING.put("MEDIUMINT", "Integer");
        TYPE_MAPPING.put("INT", "Integer");
        TYPE_MAPPING.put("INTEGER", "Integer");
        TYPE_MAPPING.put("BIGINT", "Long");
        
        // 浮点数类型
        TYPE_MAPPING.put("FLOAT", "Float");
        TYPE_MAPPING.put("DOUBLE", "Double");
        TYPE_MAPPING.put("REAL", "Double");
        TYPE_MAPPING.put("DECIMAL", "BigDecimal");
        TYPE_MAPPING.put("NUMERIC", "BigDecimal");
        TYPE_MAPPING.put("DEC", "BigDecimal");
        TYPE_MAPPING.put("FIXED", "BigDecimal");
        
        // 字符串类型
        TYPE_MAPPING.put("CHAR", "String");
        TYPE_MAPPING.put("VARCHAR", "String");
        TYPE_MAPPING.put("TINYTEXT", "String");
        TYPE_MAPPING.put("TEXT", "String");
        TYPE_MAPPING.put("MEDIUMTEXT", "String");
        TYPE_MAPPING.put("LONGTEXT", "String");
        TYPE_MAPPING.put("NCHAR", "String");
        TYPE_MAPPING.put("NVARCHAR", "String");
        TYPE_MAPPING.put("CLOB", "String");
        
        // 日期时间类型
        TYPE_MAPPING.put("DATE", "LocalDate");
        TYPE_MAPPING.put("TIME", "LocalTime");
        TYPE_MAPPING.put("DATETIME", "LocalDateTime");
        TYPE_MAPPING.put("TIMESTAMP", "LocalDateTime");
        TYPE_MAPPING.put("YEAR", "Integer");
        
        // 布尔类型
        TYPE_MAPPING.put("BOOLEAN", "Boolean");
        TYPE_MAPPING.put("BOOL", "Boolean");
        TYPE_MAPPING.put("BIT", "Boolean");
        
        // 二进制类型
        TYPE_MAPPING.put("BINARY", "byte[]");
        TYPE_MAPPING.put("VARBINARY", "byte[]");
        TYPE_MAPPING.put("TINYBLOB", "byte[]");
        TYPE_MAPPING.put("BLOB", "byte[]");
        TYPE_MAPPING.put("MEDIUMBLOB", "byte[]");
        TYPE_MAPPING.put("LONGBLOB", "byte[]");
        
        // 新版MySQL特殊类型
        TYPE_MAPPING.put("JSON", "String");
        TYPE_MAPPING.put("ENUM", "String");
        TYPE_MAPPING.put("SET", "String");
        
        // 空间数据类型
        TYPE_MAPPING.put("GEOMETRY", "String");
        TYPE_MAPPING.put("POINT", "String");
        TYPE_MAPPING.put("LINESTRING", "String");
        TYPE_MAPPING.put("POLYGON", "String");
        TYPE_MAPPING.put("MULTIPOINT", "String");
        TYPE_MAPPING.put("MULTILINESTRING", "String");
        TYPE_MAPPING.put("MULTIPOLYGON", "String");
        TYPE_MAPPING.put("GEOMETRYCOLLECTION", "String");
        
        // UUID类型
        TYPE_MAPPING.put("UUID", "String");
        
        // MySQL 8.0新增类型
        TYPE_MAPPING.put("INVISIBLE", "String");
        
        // 兼容其他数据库类型
        TYPE_MAPPING.put("SERIAL", "Long");
        TYPE_MAPPING.put("BIGSERIAL", "Long");
        TYPE_MAPPING.put("MONEY", "BigDecimal");
        TYPE_MAPPING.put("BYTEA", "byte[]");
        TYPE_MAPPING.put("INET", "String");
        TYPE_MAPPING.put("CIDR", "String");
        TYPE_MAPPING.put("MACADDR", "String");
        TYPE_MAPPING.put("INTERVAL", "String");
        TYPE_MAPPING.put("ARRAY", "String");
        TYPE_MAPPING.put("JSONB", "String");
        TYPE_MAPPING.put("XML", "String");
    }
    
    /**
     * 解析多个 CREATE TABLE 语句，返回第一个表的元数据
     * 
     * @param ddl 包含多个CREATE TABLE语句的DDL
     * @return 第一个表的元数据
     */
    public TableMetadata parseCreateTable(String ddl) {
        List<TableMetadata> tables = parseMultipleCreateTable(ddl);
        if (tables.isEmpty()) {
            throw new SqlParseException("未找到有效的CREATE TABLE语句");
        }
        return tables.get(0);
    }
    
    /**
     * 解析多个 CREATE TABLE 语句
     * 
     * @param ddl 包含多个CREATE TABLE语句的DDL
     * @return 表元数据列表
     */
    public List<TableMetadata> parseMultipleCreateTable(String ddl) {
        long startTime = System.currentTimeMillis();
        List<TableMetadata> tables = new ArrayList<>();
        
        try {
            log.info("开始解析多表DDL语句，长度: {} 字符", ddl != null ? ddl.length() : 0);
            
            // 输入验证
            if (ddl == null || ddl.trim().isEmpty()) {
                throw new SqlParseException("DDL语句不能为空");
            }
            
            // 设置当前DDL用于字段注释提取
            this.currentDdl = ddl;
            
            // 预处理DDL：清理注释
            String cleanedDdl = preprocessMultipleDdl(ddl);
            log.debug("多表DDL预处理完成，清理后长度: {} 字符", cleanedDdl.length());
            
            // 提取所有CREATE TABLE语句
            List<String> createTableStatements = extractCreateTableStatements(cleanedDdl);
            log.debug("提取到 {} 个CREATE TABLE语句", createTableStatements.size());
            
            // 解析每个CREATE TABLE语句
            for (String createTableStatement : createTableStatements) {
                try {
                    TableMetadata table = parseSingleCreateTable(createTableStatement);
                    tables.add(table);
                    log.debug("成功解析表: {}", table.getTableName());
                } catch (Exception e) {
                    log.warn("解析CREATE TABLE语句失败，跳过: {}", e.getMessage());
                }
            }
            
            long endTime = System.currentTimeMillis();
            log.info("多表DDL解析完成: 解析了{}个表, 耗时={}ms", tables.size(), endTime - startTime);
            
            return tables;
            
        } catch (SqlParseException e) {
            log.error("多表DDL解析失败: {}", e.getMessage());
            throw e;
        } catch (Exception e) {
            log.error("解析多表DDL语句时发生未预期错误: {}", e.getMessage(), e);
            throw new SqlParseException("SQL语法错误或解析器内部错误: " + e.getMessage(), e);
        } finally {
            this.currentDdl = null;
        }
    }
    
    /**
     * 解析单个 CREATE TABLE 语句
     * 
     * @param ddl 单个CREATE TABLE 语句
     * @return 表元数据
     */
    private TableMetadata parseSingleCreateTable(String ddl) {
        long startTime = System.currentTimeMillis();
        
        try {
            log.info("开始解析DDL语句，长度: {} 字符", ddl != null ? ddl.length() : 0);
            
            // 输入验证
            if (ddl == null || ddl.trim().isEmpty()) {
                throw new SqlParseException("DDL语句不能为空");
            }
            
            // 设置当前DDL用于字段注释提取
            this.currentDdl = ddl;
            
            // 预处理DDL语句
            String cleanDdl = preprocessDdl(ddl);
            log.debug("DDL预处理完成，清理后长度: {} 字符", cleanDdl.length());
            
            Statement statement = CCJSqlParserUtil.parse(cleanDdl);
            
            if (!(statement instanceof CreateTable)) {
                throw new SqlParseException("不是有效的CREATE TABLE语句，请确保语句以CREATE TABLE开头");
            }
            
            CreateTable createTable = (CreateTable) statement;
            
            // 验证表名
            String tableName = createTable.getTable().getName();
            if (tableName == null || tableName.trim().isEmpty()) {
                throw new SqlParseException("表名不能为空");
            }
            log.debug("解析到表名: {}", tableName);
            
            // 提取表注释
            String tableComment = extractTableComment(ddl);
            log.debug("提取到表注释: {}", tableComment != null ? tableComment : "无");
            
            // 提取字段信息
            List<ColumnMetadata> columns = extractColumns(createTable);
            log.debug("提取到 {} 个字段", columns.size());
            
            // 提取索引信息
            List<IndexMetadata> indexes = extractIndexes(createTable);
            log.debug("提取到 {} 个索引", indexes.size());
            
            // 构建表元数据
            TableMetadata tableMetadata = TableMetadata.builder()
                .tableName(tableName)
                .tableComment(tableComment)
                .columns(columns)
                .indexes(indexes)
                .build();
            
            // 验证字段
            if (tableMetadata.getColumns() == null || tableMetadata.getColumns().isEmpty()) {
                throw new SqlParseException("表必须至少包含一个字段");
            }
            
            // 设置主键字段
            setPrimaryKey(tableMetadata);
            
            long endTime = System.currentTimeMillis();
            log.info("DDL解析完成: 表名={}, 字段数={}, 主键={}, 耗时={}ms", 
                tableMetadata.getTableName(), 
                tableMetadata.getColumns().size(),
                tableMetadata.getPrimaryKey() != null ? tableMetadata.getPrimaryKey().getColumnName() : "无",
                endTime - startTime);
            
            return tableMetadata;
            
        } catch (SqlParseException e) {
            // 重新抛出自定义异常
            log.error("DDL解析失败: {}", e.getMessage());
            throw e;
        } catch (Exception e) {
            log.error("解析DDL语句时发生未预期错误: {}", e.getMessage(), e);
            throw new SqlParseException("SQL语法错误或解析器内部错误: " + e.getMessage(), e);
        } finally {
            // 清理当前DDL引用
            this.currentDdl = null;
        }
    }
    
    /**
     * 预处理多表DDL语句
     */
    private String preprocessMultipleDdl(String ddl) {
        if (ddl == null) {
            throw new SqlParseException("DDL语句不能为null");
        }
        
        try {
            // 去除BOM标记（如果存在）
            String cleaned = ddl;
            if (cleaned.startsWith("\uFEFF")) {
                cleaned = cleaned.substring(1);
                log.debug("移除BOM标记");
            }
            
            // 去除SQL注释（-- 和 /* */）
            cleaned = cleaned.replaceAll("--[^\r\n]*", ""); // 单行注释
            cleaned = cleaned.replaceAll("/\\*[\\s\\S]*?\\*/", ""); // 多行注释
            
            // 去除多余空格和换行，但保留必要的空格
            cleaned = cleaned.trim().replaceAll("\\s+", " ");
            
            log.debug("多表DDL预处理完成，原长度: {}, 处理后长度: {}", ddl.length(), cleaned.length());
            return cleaned;
            
        } catch (Exception e) {
            log.error("多表DDL预处理失败: {}", e.getMessage());
            throw new SqlParseException("多表DDL预处理失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 从清理后的DDL中提取所有CREATE TABLE语句
     */
    private List<String> extractCreateTableStatements(String cleanedDdl) {
        List<String> statements = new ArrayList<>();
        
        try {
            // 按分号分割语句
            String[] parts = cleanedDdl.split(";");
            
            for (String part : parts) {
                String trimmed = part.trim();
                if (trimmed.isEmpty()) {
                    continue;
                }
                
                // 检查是否是CREATE TABLE语句
                if (trimmed.toUpperCase().startsWith("CREATE TABLE")) {
                    // 确保语句以分号结尾
                    if (!trimmed.endsWith(";")) {
                        trimmed += ";";
                    }
                    statements.add(trimmed);
                    log.debug("提取到CREATE TABLE语句，长度: {} 字符", trimmed.length());
                }
            }
            
            return statements;
            
        } catch (Exception e) {
            log.error("提取CREATE TABLE语句失败: {}", e.getMessage());
            throw new SqlParseException("提取CREATE TABLE语句失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 预处理单个DDL语句
     */
    private String preprocessDdl(String ddl) {
        if (ddl == null) {
            throw new SqlParseException("DDL语句不能为null");
        }
        
        try {
            // 去除BOM标记（如果存在）
            String cleaned = ddl;
            if (cleaned.startsWith("\uFEFF")) {
                cleaned = cleaned.substring(1);
                log.debug("移除BOM标记");
            }
            
            // 去除SQL注释（-- 和 /* */）
            cleaned = cleaned.replaceAll("--[^\r\n]*", ""); // 单行注释
            cleaned = cleaned.replaceAll("/\\*[\\s\\S]*?\\*/", ""); // 多行注释
            
            // 处理生成列语法 - 更精确地匹配嵌套括号
            cleaned = processGeneratedColumns(cleaned);
            
            // 去除多余空格和换行，但保留必要的空格
            cleaned = cleaned.trim().replaceAll("\\s+", " ");
            
            // 验证是否为CREATE TABLE语句
            if (!cleaned.toUpperCase().startsWith("CREATE TABLE")) {
                throw new SqlParseException("不是有效的CREATE TABLE语句");
            }
            
            // 确保语句以分号结尾
            if (!cleaned.endsWith(";")) {
                cleaned += ";";
                log.debug("添加结尾分号");
            }
            
            // 验证基本的括号匹配
            int openParens = 0;
            for (char c : cleaned.toCharArray()) {
                if (c == '(') openParens++;
                else if (c == ')') openParens--;
                if (openParens < 0) {
                    throw new SqlParseException("SQL语句括号不匹配：多余的右括号");
                }
            }
            if (openParens > 0) {
                throw new SqlParseException("SQL语句括号不匹配：缺少右括号");
            }
            
            log.debug("DDL预处理完成，原长度: {}, 处理后长度: {}", ddl.length(), cleaned.length());
            log.debug("预处理后的DDL: {}", cleaned.length() > 500 ? cleaned.substring(0, 500) + "..." : cleaned);
            return cleaned;
            
        } catch (SqlParseException e) {
            throw e;
        } catch (Exception e) {
            log.error("DDL预处理失败: {}", e.getMessage());
            throw new SqlParseException("DDL预处理失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 处理生成列语法，移除 GENERATED ALWAYS AS (expression) STORED
     * 支持嵌套括号的复杂表达式
     */
    private String processGeneratedColumns(String ddl) {
        // 查找所有 GENERATED ALWAYS AS 的位置
        Pattern pattern = Pattern.compile("(?i)\\s+GENERATED\\s+ALWAYS\\s+AS\\s*\\(", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(ddl);
        
        StringBuilder result = new StringBuilder();
        int lastEnd = 0;
        
        while (matcher.find()) {
            // 添加匹配之前的内容
            result.append(ddl.substring(lastEnd, matcher.start()));
            
            // 找到匹配的右括号（考虑嵌套）
            int startPos = matcher.end() - 1; // 指向开始的 '('
            int parenCount = 1;
            int currentPos = startPos + 1;
            
            while (currentPos < ddl.length() && parenCount > 0) {
                char c = ddl.charAt(currentPos);
                if (c == '(') {
                    parenCount++;
                } else if (c == ')') {
                    parenCount--;
                }
                currentPos++;
            }
            
            if (parenCount == 0) {
                // 找到了匹配的右括号，继续查找 STORED
                String remaining = ddl.substring(currentPos).trim();
                if (remaining.toUpperCase().startsWith("STORED")) {
                    // 跳过 STORED 关键字
                    currentPos += remaining.toLowerCase().indexOf("stored") + 6;
                    result.append(" /* GENERATED_COLUMN */");
                    lastEnd = currentPos;
                } else {
                    // 没有找到 STORED，保留原始内容
                    result.append(ddl.substring(matcher.start(), currentPos));
                    lastEnd = currentPos;
                }
            } else {
                // 括号不匹配，保留原始内容
                result.append(ddl.substring(matcher.start(), matcher.end()));
                lastEnd = matcher.end();
            }
        }
        
        // 添加剩余内容
        result.append(ddl.substring(lastEnd));
        
        String processed = result.toString();
        if (!processed.equals(ddl)) {
            log.debug("处理了生成列语法，原长度: {}, 处理后长度: {}", ddl.length(), processed.length());
        }
        
        return processed;
    }
    
    /**
     * 提取字段信息
     */
    private List<ColumnMetadata> extractColumns(CreateTable createTable) {
        List<ColumnMetadata> columns = new ArrayList<>();
        
        if (createTable.getColumnDefinitions() != null) {
            for (ColumnDefinition columnDef : createTable.getColumnDefinitions()) {
                ColumnMetadata column = buildColumnMetadata(columnDef);
                columns.add(column);
            }
        }
        
        return columns;
    }
    
    /**
     * 构建字段元数据
     */
    private ColumnMetadata buildColumnMetadata(ColumnDefinition columnDef) {
        try {
            String columnName = columnDef.getColumnName();
            if (columnName == null || columnName.trim().isEmpty()) {
                throw new SqlParseException("字段名不能为空");
            }
            
            String dataType = columnDef.getColDataType().getDataType().toUpperCase();
            List<String> argumentsStringList = columnDef.getColDataType().getArgumentsStringList();
            
            // 提取各种属性
            String columnComment = extractColumnComment(columnDef);
            String javaType = getJavaType(dataType);
            Integer length = getColumnLength(argumentsStringList);
            Integer scale = getColumnScale(argumentsStringList);
            boolean nullable = !hasNotNullConstraint(columnDef);
            boolean primaryKey = hasPrimaryKeyConstraint(columnDef);
            boolean autoIncrement = hasAutoIncrementConstraint(columnDef);
            String defaultValue = getDefaultValue(columnDef);
            
            // 生成Java字段名和属性名
            String javaFieldName = toCamelCase(columnName);
            String javaPropertyName = toPascalCase(columnName);
            
            log.debug("构建字段元数据: name={}, type={}, javaType={}, length={}, scale={}, nullable={}, pk={}, ai={}, default={}, comment={}, fieldName={}, propertyName={}",
                columnName, dataType, javaType, length, scale, nullable, primaryKey, autoIncrement, defaultValue, columnComment, javaFieldName, javaPropertyName);
            
            ColumnMetadata column = ColumnMetadata.builder()
                .columnName(columnName)
                .columnComment(columnComment)
                .dataType(dataType)
                .javaType(javaType)
                .length(length)
                .scale(scale)
                .nullable(nullable)
                .primaryKey(primaryKey)
                .autoIncrement(autoIncrement)
                .defaultValue(defaultValue)
                .javaFieldName(javaFieldName)
                .javaPropertyName(javaPropertyName)
                .build();
            
            log.debug("字段Java属性名设置完成: fieldName={}, propertyName={}", javaFieldName, javaPropertyName);
            
            return column;
            
        } catch (Exception e) {
            String columnName = columnDef != null ? columnDef.getColumnName() : "unknown";
            log.error("构建字段元数据失败: columnName={}, error={}", columnName, e.getMessage());
            throw new SqlParseException("构建字段元数据失败: " + columnName + ", " + e.getMessage(), e);
        }
    }
    
    /**
     * 获取Java类型
     */
    private String getJavaType(String dataType) {
        if (dataType == null) {
            return "String";
        }
        
        // 处理带括号的类型，如VARCHAR(255)，只取类型名部分
        String baseType = dataType.toUpperCase();
        int parenPos = baseType.indexOf('(');
        if (parenPos > 0) {
            baseType = baseType.substring(0, parenPos);
        }
        
        // 处理UNSIGNED类型
        if (baseType.contains("UNSIGNED")) {
            baseType = baseType.replace("UNSIGNED", "").trim();
            // 对于UNSIGNED整数类型，使用更大的Java类型
            switch (baseType) {
                case "TINYINT":
                    return "Integer";  // TINYINT UNSIGNED -> Integer
                case "SMALLINT":
                    return "Integer";  // SMALLINT UNSIGNED -> Integer  
                case "MEDIUMINT":
                    return "Long";     // MEDIUMINT UNSIGNED -> Long
                case "INT":
                case "INTEGER":
                    return "Long";     // INT UNSIGNED -> Long
                case "BIGINT":
                    return "BigDecimal"; // BIGINT UNSIGNED -> BigDecimal (避免溢出)
            }
        }
        
        // 处理ZEROFILL类型
        if (baseType.contains("ZEROFILL")) {
            baseType = baseType.replace("ZEROFILL", "").trim();
        }
        
        // 特殊处理BIT类型
        if (baseType.startsWith("BIT")) {
            return "Boolean"; // BIT(1) -> Boolean, BIT(n) -> Boolean (简化处理)
        }
        
        // 特殊处理ENUM和SET类型
        if (baseType.startsWith("ENUM") || baseType.startsWith("SET")) {
            return "String";
        }
        
        return TYPE_MAPPING.getOrDefault(baseType, "String");
    }
    
    /**
     * 获取字段长度
     */
    private Integer getColumnLength(List<String> arguments) {
        if (arguments != null && !arguments.isEmpty()) {
            try {
                return Integer.parseInt(arguments.get(0));
            } catch (NumberFormatException e) {
                // 忽略解析错误
            }
        }
        return null;
    }
    
    /**
     * 获取小数位数
     */
    private Integer getColumnScale(List<String> arguments) {
        if (arguments != null && arguments.size() > 1) {
            try {
                return Integer.parseInt(arguments.get(1));
            } catch (NumberFormatException e) {
                // 忽略解析错误
            }
        }
        return null;
    }
    
    /**
     * 检查是否有NOT NULL约束
     */
    private boolean hasNotNullConstraint(ColumnDefinition columnDef) {
        // 方法1：检查columnSpecs
        if (columnDef.getColumnSpecs() != null) {
            for (String spec : columnDef.getColumnSpecs()) {
                if (spec != null && spec.toUpperCase().contains("NOT NULL")) {
                    log.debug("通过columnSpecs检测到NOT NULL约束: {}", spec);
                    return true;
                }
            }
        }
        
        // 方法2：从原始DDL中检查NOT NULL约束
        if (currentDdl != null && columnDef.getColumnName() != null) {
            String columnName = columnDef.getColumnName();
            String escapedColumnName = Pattern.quote(columnName);
            
            // 检查该字段定义中是否包含NOT NULL
            String pattern = "\\b" + escapedColumnName + "\\s+[^,()]*?\\bNOT\\s+NULL\\b";
            Pattern notNullPattern = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
            Matcher matcher = notNullPattern.matcher(currentDdl);
            
            if (matcher.find()) {
                log.debug("通过DDL解析检测到字段{}的NOT NULL约束", columnName);
                return true;
            }
        }
        
        return false;
    }
    
    /**
     * 检查是否有PRIMARY KEY约束
     */
    private boolean hasPrimaryKeyConstraint(ColumnDefinition columnDef) {
        if (columnDef.getColumnSpecs() != null) {
            // 检查是否包含PRIMARY KEY关键字
            for (String spec : columnDef.getColumnSpecs()) {
                if (spec != null && spec.toUpperCase().contains("PRIMARY KEY")) {
                    return true;
                }
            }
        }
        
        // 额外检查：从原始DDL中查找主键定义
        String columnName = columnDef.getColumnName();
        if (currentDdl != null && columnName != null) {
            // 检查字段级PRIMARY KEY定义
            String escapedColumnName = Pattern.quote(columnName);
            String[] patterns = {
                "\\b" + escapedColumnName + "\\s+[^,()]*?PRIMARY\\s+KEY",
                "\\b" + escapedColumnName + "\\s+[^,()]*?PRIMARY KEY"
            };
            
            for (String pattern : patterns) {
                Pattern pkPattern = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE);
                if (pkPattern.matcher(currentDdl).find()) {
                    log.debug("从DDL中识别到主键字段: {}", columnName);
                    return true;
                }
            }
        }
        
        return false;
    }
    
    /**
     * 检查是否有AUTO_INCREMENT约束
     */
    private boolean hasAutoIncrementConstraint(ColumnDefinition columnDef) {
        if (columnDef.getColumnSpecs() != null) {
            // 检查是否包含AUTO_INCREMENT关键字
            for (String spec : columnDef.getColumnSpecs()) {
                if (spec != null && spec.toUpperCase().contains("AUTO_INCREMENT")) {
                    return true;
                }
            }
        }
        return false;
    }
    
    /**
     * 获取默认值
     */
    private String getDefaultValue(ColumnDefinition columnDef) {
        if (columnDef.getColumnSpecs() != null) {
            for (String spec : columnDef.getColumnSpecs()) {
                if (spec != null) {
                    String upperSpec = spec.toUpperCase();
                    
                    // 检查是否包含DEFAULT关键字
                    if (upperSpec.contains("DEFAULT")) {
                        try {
                            // 提取DEFAULT后面的值
                            String defaultPart = spec.substring(spec.toUpperCase().indexOf("DEFAULT") + 7).trim();
                            
                            // 处理各种默认值格式
                            if (defaultPart.startsWith("'") && defaultPart.contains("'")) {
                                // 字符串默认值：DEFAULT 'value'
                                int endQuote = defaultPart.indexOf("'", 1);
                                if (endQuote > 0) {
                                    return defaultPart.substring(1, endQuote);
                                }
                            } else if (defaultPart.startsWith("\"") && defaultPart.contains("\"")) {
                                // 双引号字符串默认值：DEFAULT "value"
                                int endQuote = defaultPart.indexOf("\"", 1);
                                if (endQuote > 0) {
                                    return defaultPart.substring(1, endQuote);
                                }
                            } else {
                                // 数字或关键字默认值：DEFAULT 0, DEFAULT NULL, DEFAULT CURRENT_TIMESTAMP等
                                String[] parts = defaultPart.split("\\s+");
                                if (parts.length > 0 && !parts[0].isEmpty()) {
                                    String value = parts[0];
                                    // 移除可能的逗号或其他符号
                                    value = value.replaceAll("[,;)]", "");
                                    return value.isEmpty() ? null : value;
                                }
                            }
                        } catch (Exception e) {
                            log.warn("解析默认值失败: {}, spec: {}", e.getMessage(), spec);
                        }
                    }
                }
            }
        }
        return null;
    }
    
    /**
     * 提取表注释
     */
    private String extractTableComment(String ddl) {
        if (ddl == null || ddl.trim().isEmpty()) {
            return null;
        }
        
        // 移除注释和多余空白符，规范化DDL
        String cleanDdl = ddl.replaceAll("--[^\r\n]*", "").replaceAll("/\\*.*?\\*/", "").trim();
        
        // 多种表注释格式的正则表达式
        String[] patterns = {
            // 标准格式：) COMMENT '注释'
            "\\)\\s*COMMENT\\s*['\"]([^'\"]*)['\"]\\s*;?\\s*$",
            // 格式2：) COMMENT='注释'
            "\\)\\s*COMMENT\\s*=\\s*['\"]([^'\"]*)['\"]\\s*;?\\s*$",
            // 格式3：表定义结束后的 COMMENT
            "\\)\\s*[^'\"]*COMMENT\\s*['\"]([^'\"]*)['\"]\\s*;?\\s*$"
        };
        
        for (String patternStr : patterns) {
            Pattern pattern = Pattern.compile(patternStr, Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
            Matcher matcher = pattern.matcher(cleanDdl);
            if (matcher.find()) {
                String comment = matcher.group(1);
                if (comment != null && !comment.trim().isEmpty()) {
                    log.debug("成功提取表注释: {}", comment.trim());
                    return comment.trim();
                }
            }
        }
        
        log.debug("未能提取到表注释，DDL: {}", cleanDdl.length() > 200 ? cleanDdl.substring(0, 200) + "..." : cleanDdl);
        return null;
    }
    
    /** 当前解析的DDL语句 */
    private String currentDdl;
    
    /**
     * 提取字段注释
     */
    private String extractColumnComment(ColumnDefinition columnDef) {
        // 从原始DDL中提取字段注释
        String columnName = columnDef.getColumnName();
        return extractColumnCommentFromDdl(columnName);
    }
    
    /**
     * 从DDL中提取字段注释
     */
    private String extractColumnCommentFromDdl(String columnName) {
        if (currentDdl == null || columnName == null) {
            return null;
        }
        
        try {
            // 移除SQL注释，只保留CREATE TABLE定义
            String cleanDdl = currentDdl.replaceAll("--[^\r\n]*", "").replaceAll("/\\*.*?\\*/", "");
            
            // 转义特殊字符，防止正则表达式错误
            String escapedColumnName = Pattern.quote(columnName);
            
            // 更精确的注释提取正则表达式
            String[] patterns = {
                // 标准格式：column_name TYPE [约束] COMMENT 'comment' [,)]
                "\\b" + escapedColumnName + "\\s+[^,()]+?COMMENT\\s*['\"]([^'\"]*)['\"]\\s*(?:,|\\))",
                // 处理行末的注释
                "\\b" + escapedColumnName + "\\s+[^,()\\r\\n]+?COMMENT\\s*['\"]([^'\"]*)['\"]\\s*(?:,|\\)|$|\\r|\\n)",
                // 更宽松的匹配，处理复杂的列定义
                "\\b" + escapedColumnName + "\\s+(?:[^,()]*?)COMMENT\\s*['\"]([^'\"]*)['\"]"
            };
            
            for (String pattern : patterns) {
                Pattern commentPattern = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE | Pattern.MULTILINE | Pattern.DOTALL);
                Matcher matcher = commentPattern.matcher(cleanDdl);
                
                if (matcher.find()) {
                    String comment = matcher.group(1);
                    if (comment != null && !comment.trim().isEmpty()) {
                        log.debug("成功提取字段{}的注释: {}", columnName, comment.trim());
                        return comment.trim();
                    }
                }
            }
            
            log.debug("未能提取到字段{}的注释", columnName);
            
        } catch (Exception e) {
            log.warn("提取字段{}注释时发生异常: {}", columnName, e.getMessage());
        }
        
        return null;
    }
    
    /**
     * 提取索引信息
     */
    private List<IndexMetadata> extractIndexes(CreateTable createTable) {
        List<IndexMetadata> indexes = new ArrayList<>();
        
        if (createTable.getIndexes() != null) {
            for (Index index : createTable.getIndexes()) {
                IndexMetadata indexMetadata = IndexMetadata.builder()
                    .indexName(index.getName())
                    .unique("UNIQUE".equalsIgnoreCase(index.getType()))
                    .columnNames(index.getColumnsNames())
                    .build();
                indexes.add(indexMetadata);
            }
        }
        
        return indexes;
    }
    
    /**
     * 设置主键字段
     */
    private void setPrimaryKey(TableMetadata tableMetadata) {
        if (tableMetadata.getColumns() != null) {
            List<ColumnMetadata> primaryKeyColumns = new ArrayList<>();
            
            // 首先检查字段级别的主键定义
            for (ColumnMetadata column : tableMetadata.getColumns()) {
                if (Boolean.TRUE.equals(column.getPrimaryKey())) {
                    primaryKeyColumns.add(column);
                    log.debug("找到字段级主键: {}", column.getColumnName());
                }
            }
            
            // 如果没有找到字段级主键，检查表级PRIMARY KEY约束
            if (primaryKeyColumns.isEmpty()) {
                List<String> primaryKeyNames = extractTableLevelPrimaryKeys();
                if (!primaryKeyNames.isEmpty()) {
                    for (String primaryKeyName : primaryKeyNames) {
                        for (ColumnMetadata column : tableMetadata.getColumns()) {
                            if (primaryKeyName.equalsIgnoreCase(column.getColumnName())) {
                                column.setPrimaryKey(true);
                                primaryKeyColumns.add(column);
                                log.debug("找到表级主键: {}", column.getColumnName());
                                break;
                            }
                        }
                    }
                }
            }
            
            // 设置主键信息
            if (!primaryKeyColumns.isEmpty()) {
                if (primaryKeyColumns.size() == 1) {
                    // 单字段主键
                    tableMetadata.setPrimaryKey(primaryKeyColumns.get(0));
                    tableMetadata.setPrimaryKeys(primaryKeyColumns);
                    tableMetadata.setCompositePrimaryKey(false);
                    log.debug("设置单字段主键: {}", primaryKeyColumns.get(0).getColumnName());
                } else {
                    // 复合主键 - 选择第一个字段作为主要主键用于代码生成
                    tableMetadata.setPrimaryKey(primaryKeyColumns.get(0));
                    tableMetadata.setPrimaryKeys(primaryKeyColumns);
                    tableMetadata.setCompositePrimaryKey(true);
                    log.debug("设置复合主键（使用第一个字段作为主要主键）: {}", primaryKeyColumns.get(0).getColumnName());
                    log.debug("完整复合主键字段: {}", primaryKeyColumns.stream()
                        .map(ColumnMetadata::getColumnName)
                        .collect(java.util.stream.Collectors.joining(", ")));
                }
            } else {
                log.debug("未找到主键定义");
            }
        }
    }
    
    /**
     * 提取表级PRIMARY KEY约束
     */
    private List<String> extractTableLevelPrimaryKeys() {
        if (currentDdl == null) {
            return new ArrayList<>();
        }
        
        try {
            // 匹配表级PRIMARY KEY约束：PRIMARY KEY (column_name) 或 PRIMARY KEY (col1, col2)
            String[] patterns = {
                "PRIMARY\\s+KEY\\s*\\(\\s*([a-zA-Z_][a-zA-Z0-9_,\\s]*)\\s*\\)",
                "CONSTRAINT\\s+[a-zA-Z_][a-zA-Z0-9_]*\\s+PRIMARY\\s+KEY\\s*\\(\\s*([a-zA-Z_][a-zA-Z0-9_,\\s]*)\\s*\\)"
            };
            
            for (String pattern : patterns) {
                Pattern pkPattern = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE);
                Matcher matcher = pkPattern.matcher(currentDdl);
                
                if (matcher.find()) {
                    String columnNames = matcher.group(1).trim();
                    log.debug("提取到表级主键字段: {}", columnNames);
                    
                    // 解析所有主键字段
                    List<String> primaryKeys = new ArrayList<>();
                    if (columnNames.contains(",")) {
                        // 复合主键
                        String[] columns = columnNames.split(",");
                        for (String column : columns) {
                            String trimmedColumn = column.trim();
                            if (!trimmedColumn.isEmpty()) {
                                primaryKeys.add(trimmedColumn);
                            }
                        }
                        log.debug("检测到复合主键: {}", primaryKeys);
                    } else {
                        // 单字段主键
                        primaryKeys.add(columnNames);
                        log.debug("检测到单字段主键: {}", columnNames);
                    }
                    return primaryKeys;
                }
            }
            
            // 额外检查：查找字段级PRIMARY KEY定义
            String[] fieldLevelPatterns = {
                "([a-zA-Z_][a-zA-Z0-9_]*)\\s+[^,()]*?PRIMARY\\s+KEY",
                "([a-zA-Z_][a-zA-Z0-9_]*)\\s+[^,()]*?PRIMARY KEY"
            };
            
            for (String pattern : fieldLevelPatterns) {
                Pattern pkPattern = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE);
                Matcher matcher = pkPattern.matcher(currentDdl);
                
                while (matcher.find()) {
                    String columnName = matcher.group(1);
                    log.debug("提取到字段级主键字段: {}", columnName);
                    List<String> fieldLevelPks = new ArrayList<>();
                    fieldLevelPks.add(columnName);
                    return fieldLevelPks;
                }
            }
            
        } catch (Exception e) {
            log.warn("提取表级主键失败: {}", e.getMessage());
        }
        
        return new ArrayList<>();
    }
    
    /**
     * 下划线转驼峰
     */
    private String toCamelCase(String str) {
        if (str == null || str.isEmpty()) {
            return str;
        }
        
        StringBuilder result = new StringBuilder();
        boolean toUpper = false;
        
        for (char c : str.toCharArray()) {
            if (c == '_') {
                toUpper = true;
            } else {
                if (toUpper) {
                    result.append(Character.toUpperCase(c));
                    toUpper = false;
                } else {
                    result.append(Character.toLowerCase(c));
                }
            }
        }
        
        return result.toString();
    }
    
    /**
     * 转换为PascalCase
     */
    private String toPascalCase(String str) {
        String camelCase = toCamelCase(str);
        if (camelCase == null || camelCase.isEmpty()) {
            return camelCase;
        }
        return camelCase.substring(0, 1).toUpperCase() + camelCase.substring(1);
    }
}
